import java.sql.*;
import org.apache.derby.jdbc.ClientDriver;

import java.util.InputMismatchException;
import java.util.Scanner;

public class MovieDb {
	private static Connection conn = null;
	
	public static void main(String[] args) {
		Scanner keyboard = new Scanner(System.in);
		System.out.println();
		
		while (true) {
			int choice = menu();
			switch (choice) {
				case 999:
					admin();
					break;
				case 1:
					System.out.print("Year: ");
					int year = keyboard.nextInt();
					String query3 = "select title, mYear, rating from MOVIE where mYear=" + year;
					getResult(query3);
					break;
				case 2:
					System.out.print("Rating: ");
					String rating = keyboard.nextLine().toUpperCase();
					String query4 = "select title, mYear, rating from MOVIE where UPPER(rating) ='" + rating + "'";
					getResult(query4);
					break;
				case 3:
					System.out.print("Actor name: ");
					String actorName = keyboard.nextLine();
					try {
					String actorNameArray[] = actorName.split(" ");
					String actorFirstName = actorNameArray[0].toUpperCase();
					String actorLastName = actorNameArray[1].toUpperCase();
					String query5 = "select m.title, m.mYear, m.rating from MOVIE m where m.MovieId in " +
							"(select r.movieId from ROLE r where r.ActorId in" +
							"(select a.ActorId from ACTOR a where UPPER(a.fname) = '" + actorFirstName + "' " +
									"and UPPER(a.lname) = '" + actorLastName + "'))";
					getResult(query5);
					}
					catch (Exception e) {
						System.out.println("Please enter the full name!");
					}
					break;
				case 4:
					System.out.print("Director name: ");
					String directorName = keyboard.nextLine();
					try {
					String directorNameArray[] = directorName.split(" ");
					String directorFirstName = directorNameArray[0].toUpperCase();
					String directorLastName = directorNameArray[1].toUpperCase();
					System.out.println(directorFirstName + " " + directorLastName);
					String query6 = "select m.title, m.mYear, m.rating from MOVIE m where m.DirectorId in " +
							"(select d.DirectorId from DIRECTOR d where UPPER(d.fname) = '" + directorFirstName +
							"' and UPPER(d.lname) = '" + directorLastName + "')";
					getResult(query6);
					}
					catch (Exception e) {
						System.out.println("Please enter the full name!");
					}
					break;
				case 5:
					System.out.print("Writer name: ");
					String writerName = keyboard.nextLine();
					try {
					String writerNameArray[] = writerName.split(" ");
					String writerFirstName = writerNameArray[0].toUpperCase();
					String writerLastName = writerNameArray[1].toUpperCase();
					System.out.println(writerFirstName + " " + writerLastName);
					String query7 = "select m.title from MOVIE m where m.WriterId in " +
							"(select w.WriteId from WRITER w where UPPER(w.fname) = '" + writerFirstName +
							"' and UPPER(w.lname) = '" + writerLastName + "')";
					getResult(query7);
					}
					catch (Exception e) {
						System.out.println("Please enter the full name!");
					}
					break;
				case 6:
					System.out.println("Studio name: ");
					String studio = keyboard.nextLine().toUpperCase();
					String query8 = "select m.title from MOVIE m where m.StudioId in " +
							"(select s.StudioId from STUDIO s where UPPER(s.name) = '" + studio + "')";
					getResult(query8);
					break;
				case 7:
					System.out.println("Movie name: ");
					String movieName = keyboard.nextLine().toUpperCase();
					String query9 = "select title, mYear, rating from MOVIE where UPPER(title) like '%"
							+ movieName + "%'";
					getResult(query9);
					break;
				case 0:
					System.exit(0);
				default:
					break;
			}
		}	

	}
	
	public static int menu() {
		System.out.println("-----------------------------");
		System.out.println("Movie DB");
		System.out.println("1. List all movies in a given year");
		System.out.println("2. List all movies by rating");
		System.out.println("3. List all movies featuring a given actor");
		System.out.println("4. List all movies directed by a given director");
		System.out.println("5. List all movies written by a given writer");
		System.out.println("6. List all movies made by a given studio");
		System.out.println("7. Search for a movie");
		System.out.println("0. Exit program");
		System.out.println("-----------------------------");
		System.out.print("Please select an option: ");
		Scanner keyboard = new Scanner(System.in);
		boolean goodInput = false;
		int choice = goodInputInt(goodInput, keyboard);
		return choice;
	}
	
	public static void admin() {
		Scanner keyboard = new Scanner(System.in);
		int choice = adminMenu();
		switch (choice) {
		case 1:
			System.out.print("Title: ");
			String newMovieTitle = keyboard.nextLine();
			System.out.print("Release Year: ");
			String newMovieYear = keyboard.next();
			int nmYear = Integer.parseInt(newMovieYear);
			System.out.print("Genre: ");
			String newMovieGenre = keyboard.next();
			System.out.print("Writer ID: ");
			String newMovieWriterId = keyboard.next();
			int nmwId = Integer.parseInt(newMovieWriterId);
			System.out.print("Director ID: ");
			String newMovieDirectorId = keyboard.next();
			int nmdId = Integer.parseInt(newMovieDirectorId);
			System.out.print("Studio ID: ");
			String newMovieStudioId = keyboard.next();
			int nmsId = Integer.parseInt(newMovieStudioId);
			System.out.print("Rating: ");
			String newMovieRating = keyboard.next();
			String query1 = "insert into MOVIE (title, mYear, genre, WriterId, DirectorId, StudioId, rating)"
							+ " values (" + newMovieTitle + ", " + nmYear + ", " + newMovieGenre + ", " + nmwId + ", "
							+ nmdId + ", " + nmsId + ", " + newMovieRating + ")";
			doQuery(query1);
			break;
		case 2:
			System.out.print("First name: ");
			String newActorFName = keyboard.nextLine();
			System.out.print("Last name: ");
			String newActorLName = keyboard.nextLine();
			String query2 = "insert into ACTOR (fname, lname)"
							+ " values ('" + newActorFName + "', '" + newActorLName + "')";
			doQuery(query2);
			break; 
		case 3:
			System.out.print("First Name: ");
			String newWriterFName = keyboard.nextLine();
			System.out.print("Last name: ");
			String newWriterLName = keyboard.nextLine();
			String query3 = "insert into WRITER (fname, lname)"
							+ " values ('" + newWriterFName + "', '" + newWriterLName + "')";
			doQuery(query3);
			break;
		case 4:
			System.out.print("First name: ");
			String newDirectorFName = keyboard.nextLine();
			System.out.print("Last name: ");
			String newDirectorLName = keyboard.nextLine();
			String query4 = "insert into DIRECTOR (fname, lname)"
							+ " values ('" + newDirectorFName + "', '" + newDirectorLName + "')";
			doQuery(query4);
			break;
		case 5:
			System.out.print("Name: ");
			String newStudioName = keyboard.nextLine();
			System.out.print("Location (10 character limit): ");
			String newStudioLocation = keyboard.nextLine();
			String query5 = "insert into STUDIO (name, location)"
							+ " values ('" + newStudioName + "', '" + newStudioLocation + "')";
			doQuery(query5);
			break;
		default:
			break;
		}
	}
	
	public static int adminMenu() {
		System.out.println("Admin");
		System.out.println("1. Insert a new movie");
		System.out.println("2. Insert a new actor");
		System.out.println("3. Insert a new writer");
		System.out.println("4. Insert a new director");
		System.out.println("5. Insert a new studio");
		System.out.println("0. Back");
		System.out.print("Please select an option: ");
		Scanner keyboard = new Scanner(System.in);
		boolean goodInput = false;
		int choice = goodInputInt(goodInput, keyboard);
		return choice;
	}
	
	public static int goodInputInt(boolean goodInput, Scanner keyboard) {
		int tempInput = 0;
		while(goodInput == false) {
			try {
				tempInput = keyboard.nextInt();
				goodInput = true;
			}
			catch (InputMismatchException e) {
				keyboard.nextLine();
				System.out.print("Please re-enter value: ");
				goodInput = false;
			}
		}	
		return tempInput;
	}
	
	public static void getResult(String query) {
		try {
			Statement stmt;
			
			// connect to database server
			Driver d = new ClientDriver();
			String url = "jdbc:derby://localhost:4567/moviedb";
			conn = d.connect(url, null);

			// build and execute the query
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			ResultSetMetaData md = rs.getMetaData();
			int numcols = md.getColumnCount();
			int totalwidth = 0;
		
			// print header
			for (int i = numcols; i >= 1; i--) {
				int width;
				if (md.getColumnName(i).equalsIgnoreCase("TITLE"))
					width = 60;
				else
					width = md.getColumnDisplaySize(i);
				totalwidth += width;
				String fmt = "%" + width + "s";
				System.out.format(fmt,  md.getColumnName(i));
			}
			System.out.println();
			for (int i = 0; i < totalwidth; i++)
				System.out.print("-");
			System.out.println();
		
			// prints records
			while (rs.next()) {
				for (int i = numcols; i >= 1; i--) {
					String fldname = md.getColumnName(i);
					String fmt;
					int fldtype = md.getColumnType(i);
					if (fldname.equalsIgnoreCase("TITLE"))
						fmt = "%60";
					else
						fmt = "%" + md.getColumnDisplaySize(i);
					if (fldtype == Types.INTEGER)
						System.out.format(fmt + "d", rs.getInt(fldname));
					else
						System.out.format(fmt + "s", rs.getString(fldname));
				}
				System.out.println();
			}
			rs.close();
		}
		catch (SQLException e) {
			System.out.println("SQL Exception: " + e.getMessage());
			e.printStackTrace();
		}
	}

	public static void doQuery(String query) {
		Statement stmt;
		try {
			// connect to database server
			Driver d = new ClientDriver();
			String url = "jdbc:derby://localhost:4567/moviedb";
			conn = d.connect(url, null);

			// build and execute the query
			stmt = conn.createStatement();
			stmt.executeUpdate(query);
		}
		
		catch(SQLException e) {
			e.printStackTrace();
		}
		
		finally {
			// Disconnect from the server
			try	{
				if(conn != null)
				conn.close();
			}
			catch(SQLException e){
				e.printStackTrace();
			}
		}
	
	}
}

